﻿
CREATE PROCEDURE [dbo].[fares_ParteneriProprii]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Se insereaza toti partenerii din alte unitati in unitatea proprie
    INSERT INTO [Parteneri]
           ([CUI],[Denumire],[IDBanca],[ContBancar],[Cont],[CodUnitate],[TipPartener],[IDJudet],[IDLocalitate]
           ,[Strada],[Nr],[AtributFiscal],[JudetRegComert],[NrRegComert],[AnRegComert],[NumeDelegat],[SerieAI],[NrAI],[MijlocTransport]
           ,[NrMijlocTransport],[CodPostal],[Telefon],[Fax])
    SELECT [CUI],[Denumire],[IDBanca],[ContBancar],[Cont],@CodUnitate,[TipPartener],[IDJudet],[IDLocalitate]
           ,[Strada],[Nr],[AtributFiscal],[JudetRegComert],[NrRegComert],[AnRegComert],[NumeDelegat],[SerieAI],[NrAI],[MijlocTransport]
           ,[NrMijlocTransport],[CodPostal],[Telefon],[Fax] FROM [Parteneri] WHERE CodUnitate<>@CodUnitate
           AND (IDPartener IN (SELECT IDPartener FROM Documente WHERE CodUnitate=@CodUnitate) OR 
				IDPartener IN (SELECT IDPartener FROM DocumentePlata WHERE CodUnitate=@CodUnitate) OR
				IDPartener IN (SELECT IDFurnizor FROM Ordonantari WHERE CodUnitate=@CodUnitate))

	--modifica partenerii in noii parteneri
	UPDATE [Documente]
		SET IDPartener = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [Documente].IDPartener AND a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[Documente].[CodUnitate]=@CodUnitate AND [Documente].IDPartener NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])
	
	-- documente plata
	UPDATE [DocumentePlata]
		SET IDPartener = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [DocumentePlata].IDPartener AND a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[DocumentePlata].[CodUnitate]=@CodUnitate AND 
	[DocumentePlata].IDPartener NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])

	--modifica partenerii in noii parteneri la ordonantari
	UPDATE [Ordonantari]
		SET IDFurnizor = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [Ordonantari].IDFurnizor and a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[Ordonantari].[CodUnitate]=@CodUnitate AND [Ordonantari].IDFurnizor NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])

	
	---sterge partenerii vechi ca sa nu se dubleze
	DELETE Parteneri FROM Parteneri a WHERE 
	CUI=a.CUI AND CodUnitate<>@CodUnitate AND a.CodUnitate = @CodUnitate AND
	(IDPartener NOT IN (SELECT IDPartener FROM Documente WHERE CodUnitate<>@CodUnitate) AND 
     IDPartener NOT IN (SELECT IDPartener FROM DocumentePlata WHERE CodUnitate<>@CodUnitate) AND
     IDPartener NOT IN (SELECT IDFurnizor FROM Ordonantari WHERE CodUnitate<>@CodUnitate))
 

END